using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Grebok.Domain;
using Grebok.Persistence;

namespace Grebok.Persistence.AdoNet
{
    /// <summary>
    /// Summary description for LockingCustomerDataMapper.
    /// </summary>
    internal class OptimisticOfflineLockingCustomerDataMapper : CustomerDataMapper
    {
        private static readonly string updateCustomerByCommandText = "UPDATE Customers SET CompanyName=@CompanyName, ContactName=@ContactName, ContactTitle=@ContactTitle, Address=@Address, City=@City, Region=@Region, PostalCode=@PostalCode, Country=@Country, Phone=@Phone, Fax=@Fax WHERE CustomerId=@CustomerId AND CompanyName=@OriginalCompanyName AND ContactName=@OriginalContactName AND ContactTitle=@OriginalContactTitle AND Address=@Address AND City=@OriginalCity AND Region=@OriginalRegion AND PostalCode=@OriginalPostalCode AND Country=@OriginalCountry AND Phone=@OriginalPhone AND Fax=@OriginalFax";

        #region ICustomerDataMapper Members

        public override void Update(Customer item)
        {
            if (!(item is CustomerProxy))
            {
                this.Create(item);
            }
            else
            {
                int affectedRows = SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString,
                    CommandType.Text,
                    updateCustomerByCommandText,
                    GetParametersByEntity(item));
                if (affectedRows == 0)
                    throw new ConcurrencyException();
            }
        }

        public override IList<Customer> GetAll()
        {
            List<Customer> customers = new List<Customer>();
            SqlDataReader customersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString,
                CommandType.Text,
                "SELECT * FROM Customers");
            while (customersReader.Read())
            {
                customers.Add(BuildDataObjectFromRawData(customersReader));
            }
            customersReader.Close();
            return customers;
        }

        public override Customer GetByKey(object customerId)
        {
            Customer customer = new UnknownCustomer();
            SqlParameter customerIdParameter = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
            customerIdParameter.Value = customerId;
            SqlDataReader customersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString,
                CommandType.Text,
                "SELECT * FROM Customers WHERE CustomerId=@CustomerId",
                new SqlParameter[] { customerIdParameter });
            if (customersReader.Read())
                customer = BuildDataObjectFromRawData(customersReader);
            return customer;
        }

        #endregion

        protected override Customer BuildDataObjectFromRawData(IDataRecord rawData)
        {
            string customerId = (string)rawData["CustomerId"];
            string companyName = (string)rawData["CompanyName"];
            string contactName = rawData["ContactName"] is DBNull ? string.Empty : (string)rawData["ContactName"];
            string contactTitle = rawData["ContactTitle"] is DBNull ? string.Empty : (string)rawData["ContactTitle"];
            string address = rawData["Address"] is DBNull ? string.Empty : (string)rawData["Address"];
            string city = rawData["City"] is DBNull ? string.Empty : (string)rawData["City"];
            string country = rawData["Country"] is DBNull ? string.Empty : (string)rawData["Country"];
            string faxNumber = rawData["fax"] is DBNull ? string.Empty : (string)rawData["fax"];
            string phoneNumber = rawData["phone"] is DBNull ? string.Empty : (string)rawData["phone"];
            string postalCode = rawData["PostalCode"] is DBNull ? string.Empty : (string)rawData["PostalCode"];
            string region = rawData["Region"] is DBNull ? string.Empty : (string)rawData["Region"];
            CustomerProxy customer = new CustomerProxy();
            
            customer.CompanyName = companyName;
            customer.ContactInfo.ContactName = contactName;
            customer.ContactInfo.ContactTitle = contactTitle;
            customer.AddressInfo.Address = address;
            customer.AddressInfo.City = city;
            customer.AddressInfo.Region = region;
            customer.AddressInfo.PostalCode = postalCode;
            customer.AddressInfo.Country = country;
            customer.PhoneNumber = phoneNumber;
            customer.FaxNumber = phoneNumber;
            
            customer.OriginalCompanyName = companyName;
            customer.OriginalContactInfo.ContactName = contactName;
            customer.OriginalContactInfo.ContactTitle = contactTitle;
            customer.OriginalAddressInfo.Address = address;
            customer.OriginalAddressInfo.City = city;
            customer.OriginalAddressInfo.Region = region;
            customer.OriginalAddressInfo.PostalCode = postalCode;
            customer.OriginalAddressInfo.Country = country;
            customer.OriginalPhoneNumber = phoneNumber;
            customer.OriginalFaxNumber = phoneNumber;
            
            customer.Id = customerId;
            return customer;
        }

        protected override SqlParameter[] GetParametersByEntity(Customer customer)
        {
            CustomerProxy item = (CustomerProxy)customer;
            SqlParameter customerOriginalIdParameter = new SqlParameter("@OriginalCustomerId", SqlDbType.NChar, 5);
            customerOriginalIdParameter.Value = item.OriginalId;
            SqlParameter companyOriginalNameParameter = new SqlParameter("@OriginalCompanyName", SqlDbType.NVarChar, 40);
            companyOriginalNameParameter.Value = item.OriginalCompanyName;
            SqlParameter contactOriginalNameParameter = new SqlParameter("@OriginalContactName", SqlDbType.NVarChar, 30);
            contactOriginalNameParameter.Value = item.ContactInfo.ContactName;
            SqlParameter contactOriginalTitleParameter = new SqlParameter("@OriginalContactTitle", SqlDbType.NVarChar, 30);
            contactOriginalTitleParameter.Value = item.ContactInfo.ContactTitle;
            SqlParameter addressOriginalParameter = new SqlParameter("@OriginalAddress", SqlDbType.NVarChar, 60);
            addressOriginalParameter.Value = item.AddressInfo.Address;
            SqlParameter cityOriginalParameter = new SqlParameter("@OriginalCity", SqlDbType.NVarChar, 15);
            cityOriginalParameter.Value = item.AddressInfo.City;
            SqlParameter regionOriginalParameter = new SqlParameter("@OriginalRegion", SqlDbType.NVarChar, 15);
            regionOriginalParameter.Value = item.AddressInfo.Region;
            SqlParameter postalOriginalCodeParameter = new SqlParameter("@OriginalPostalCode", SqlDbType.NVarChar, 10);
            postalOriginalCodeParameter.Value = item.AddressInfo.PostalCode;
            SqlParameter countryOriginalParameter = new SqlParameter("@OriginalCountry", SqlDbType.NVarChar, 15);
            countryOriginalParameter.Value = item.AddressInfo.Country;
            SqlParameter phoneOriginalParameter = new SqlParameter("@OriginalPhone", SqlDbType.NVarChar, 24);
            phoneOriginalParameter.Value = item.PhoneNumber;
            SqlParameter faxOriginalParameter = new SqlParameter("@OriginalFax", SqlDbType.NVarChar, 24);
            faxOriginalParameter.Value = item.FaxNumber;

            SqlParameter customerIdParameter = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
            customerIdParameter.Value = item.Id;
            SqlParameter companyNameParameter = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40);
            companyNameParameter.Value = item.CompanyName;
            SqlParameter contactNameParameter = new SqlParameter("@ContactName", SqlDbType.NVarChar, 30);
            contactNameParameter.Value = item.ContactInfo.ContactName;
            SqlParameter contactTitleParameter = new SqlParameter("@ContactTitle", SqlDbType.NVarChar, 30);
            contactTitleParameter.Value = item.ContactInfo.ContactTitle;
            SqlParameter addressParameter = new SqlParameter("@Address", SqlDbType.NVarChar, 60);
            addressParameter.Value = item.AddressInfo.Address;
            SqlParameter cityParameter = new SqlParameter("@City", SqlDbType.NVarChar, 15);
            cityParameter.Value = item.AddressInfo.City;
            SqlParameter regionParameter = new SqlParameter("@Region", SqlDbType.NVarChar, 15);
            regionParameter.Value = item.AddressInfo.Region;
            SqlParameter postalCodeParameter = new SqlParameter("@PostalCode", SqlDbType.NVarChar, 10);
            postalCodeParameter.Value = item.AddressInfo.PostalCode;
            SqlParameter countryParameter = new SqlParameter("@Country", SqlDbType.NVarChar, 15);
            countryParameter.Value = item.AddressInfo.Country;
            SqlParameter phoneParameter = new SqlParameter("@Phone", SqlDbType.NVarChar, 24);
            phoneParameter.Value = item.PhoneNumber;
            SqlParameter faxParameter = new SqlParameter("@Fax", SqlDbType.NVarChar, 24);
            faxParameter.Value = item.FaxNumber;

            return new SqlParameter[]{
                                         customerOriginalIdParameter, 
                                         companyOriginalNameParameter,
                                         contactOriginalNameParameter,
                                         contactOriginalTitleParameter,
                                         addressOriginalParameter, 
                                         cityOriginalParameter, 
                                         regionOriginalParameter, 
                                         postalOriginalCodeParameter, 
                                         countryOriginalParameter, 
                                         phoneOriginalParameter, 
                                         faxOriginalParameter,
                                         customerIdParameter, 
                                         companyNameParameter,
                                         contactNameParameter,
                                         contactTitleParameter,
                                         addressParameter, 
                                         cityParameter, 
                                         regionParameter, 
                                         postalCodeParameter, 
                                         countryParameter, 
                                         phoneParameter, 
                                         faxParameter
                                     };
        }

    }
}
